package com.lpy.book.dao;

import com.lpy.book.model.BorrowDetail;
import com.lpy.book.model.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class UserDao {


    public int addUser(Connection con, User user) throws Exception {
        //查询注册用户名是否存在
        String sql = "select * from user where userName=? ";

        PreparedStatement ps = (PreparedStatement) con.prepareStatement(sql);
        ps.setString(1, user.getUserName());
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            return 2;
        }

        sql = "insert into user (username,password,role,sex,phone) values (?,?,?,?,?)";
        PreparedStatement ps2 = (PreparedStatement) con.prepareStatement(sql);
        ps2.setString(1, user.getUserName());
        ps2.setString(2, user.getPassword());
        ps2.setInt(3, user.getRole());
        ps2.setString(4, user.getSex());
        ps2.setString(5, user.getPhone());
        return ps2.executeUpdate();
    }


     public User login(Connection con,User user)throws Exception {
        User resultUser = null;
        String sql = "select * from user where username=? and password=? and role = ?";
         PreparedStatement pstmt = (PreparedStatement) con.prepareStatement(sql);
         pstmt.setString(1,user.getUserName());
         pstmt.setString(2,user.getPassword());
         pstmt.setInt(3,user.getRole());
         ResultSet rs = pstmt.executeQuery();
         if(rs.next()){
             resultUser = new User();
             resultUser.setUserId(rs.getInt("id"));
             resultUser.setUserName(rs.getString("username"));
             resultUser.setSex(rs.getString("sex"));
             resultUser.setPhone(rs.getString("phone"));
         }
         return resultUser;
    }

    public static int update(Connection con, User user) throws SQLException {
        String sql="update user set username=?,password=?,sex=?,phone=? where id=?";

        PreparedStatement pstmt=(PreparedStatement) con.prepareStatement(sql);

        pstmt.setString(1, user.getUserName());
        pstmt.setString(2, user.getPassword());
        pstmt.setString(3, user.getSex());
        pstmt.setString(4, user.getPhone());
        pstmt.setInt(5, user.getUserId());

        return pstmt.executeUpdate();
    }

    public ResultSet list(Connection con, BorrowDetail borrowDetail)throws Exception{
        StringBuffer sb=new StringBuffer("SELECT bd.*,u.username,b.book_name from borrowdetail bd,user u,book b where u.id=bd.user_id and b.id=bd.book_id");
        if(borrowDetail.getUserId() != null){
            sb.append(" and u.id = ?");
        }
        if(borrowDetail.getStatus() != null){
            sb.append(" and bd.status = ?");
        }
        if(borrowDetail.getBookId() != null){
            sb.append(" and bd.book_id = ?");
        }
        sb.append("  order by bd.id");
        PreparedStatement pstmt=(PreparedStatement) con.prepareStatement(sb.toString());
        if(borrowDetail.getUserId() != null){
            pstmt.setInt(1, borrowDetail.getUserId());
        }
        if(borrowDetail.getStatus() != null && borrowDetail.getBookId() != null){
            pstmt.setInt(2, borrowDetail.getStatus());
            pstmt.setInt(3, borrowDetail.getBookId());
        }
        return pstmt.executeQuery();
    }


}
